<?php
class RunsController extends AppController {

	var $name = 'Runs';
	var $components = array('Functions');
	var $helpers = array('Batch');
	
	function beforeRender() {
		$groupid = $this->Auth->user('group_id');
		$name = 'Runs';
		IF($groupid != 1){
			$result1 = mysql_query("SELECT id FROM applications WHERE name = '$name'");
			$appid = mysql_fetch_object($result1)->id;
			$result2 = mysql_query("SELECT count(*) as count FROM groupshasrights WHERE group_id = '$groupid' AND application_id = '$appid'");
			$count = mysql_fetch_object($result2)->count;
			IF($count == 0){
				$this->Session->setFlash(__('Access Denied', true));
				$this->redirect(array('controller' => 'pages' ,'action' => 'error'));	
			}
		}
	}

	function index() {
		$this->Run->recursive = 0;
		$this->set('runs', $this->paginate());
	}

	function view($id = null) {
		if (!$id) {
			$this->Session->setFlash(__('Invalid run', true));
			$this->redirect(array('action' => 'index'));
		}
		$this->set('run', $this->Run->read(null, $id));
	}
	
	function view2($id = null) {
		if (!$id) {
			$this->Session->setFlash(__('Invalid run', true));
			$this->redirect(array('action' => 'index'));
		}
		$this->set(compact('id'));
		
		//Details
		$result = $this->Run->query("SELECT r.id, s.id, r.fileid, r.batchid, s.statusname, rhs.date, s.order FROM runs AS r, runshasstatusruns AS rhs, statuses AS s, (SELECT max(rhs.id) AS maxid, rhs.run_id AS runid FROM runshasstatusruns AS rhs GROUP BY rhs.run_id) AS x WHERE r.id = rhs.run_id AND s.id = rhs.status_id AND x.runid = r.id AND rhs.id = x.maxid AND r.id = '$id' AND statusgroup_id = 1");
		
		//Overview - MACs in Run
		$result2 = mysql_query("SELECT COUNT(*) AS count FROM runshasmacs AS rhm WHERE rhm.run_id = '$id'");
		$result2count = mysql_fetch_object($result2)->count;
		
		//Overview - MACs in Blacklist
		$result3 = mysql_query("SELECT COUNT(*) AS count FROM blacklists AS b, macs AS m, runshasmacs AS rhm WHERE b.mac_id = m.id AND m.id = rhm.mac_id AND rhm.run_id = '$id'");
		$result3count = mysql_fetch_object($result3)->count;
		$result31 = mysql_query("SELECT COUNT(*) AS count FROM blacklists AS b, macs AS m, runshasmacs AS rhm WHERE b.mac_id = m.id AND m.id = rhm.mac_id AND b.status_id = 28 AND rhm.run_id = '$id'");
		$result31count = mysql_fetch_object($result31)->count;
		
		//Overview - Exeption Cases
		$result4 = mysql_query("SELECT COUNT(*) AS count FROM exceptionscases AS e, weborders AS w WHERE e.weborder_id = w.id AND w.run_id = '$id'");
		$result4count = mysql_fetch_object($result4)->count;
		$result41 = mysql_query("SELECT COUNT(*) AS count FROM exceptionscases AS e, weborders AS w WHERE e.weborder_id = w.id AND e.status_id = 31 AND w.run_id = '$id'");
		$result41count = mysql_fetch_object($result41)->count;
		
		//Overview - POND Cases
		$result5 = mysql_query("SELECT COUNT(*) AS count FROM pondcases AS pc, partrequests AS p, weborders AS w WHERE pc.partrequest_id = p.id AND p.weborder_id = w.id AND w.run_id = '$id'");
		$result5count = mysql_fetch_object($result5)->count;
		$result51 = mysql_query("SELECT COUNT(*) AS count FROM pondcases AS pc, partrequests AS p, weborders AS w WHERE pc.partrequest_id = p.id AND p.weborder_id = w.id AND pc.status_id = 33 AND w.run_id = '$id'");
		$result51count = mysql_fetch_object($result51)->count;			
		
		//Overview - Web & MMS Exceptions
		$result6 = mysql_query("SELECT COUNT(*) AS count FROM blacklists AS b, macs AS m, runshasmacs AS rhm WHERE b.mac_id = m.id AND m.id = rhm.mac_id AND b.problem_id = 12 AND rhm.run_id = '$id'");
		$result6count = mysql_fetch_object($result6)->count;
		$result61 = mysql_query("SELECT COUNT(*) AS count FROM blacklists AS b, macs AS m, runshasmacs AS rhm WHERE b.mac_id = m.id AND m.id = rhm.mac_id AND b.problem_id = 12 AND b.status_id = 28 AND run_id = '$id'");
		$result61count = mysql_fetch_object($result61)->count;
		
		
		//Blacklist
		$result10 = $this->Run->query("SELECT p.id, p.name, count(*) AS count FROM blacklists AS bl, runshasmacs AS rhm, problems AS p WHERE bl.mac_id = rhm.mac_id AND bl.problem_id = p.id AND rhm.run_id = '$id' GROUP by p.name ORDER BY p.id ASC");
		
		
		$status1 = "Closed"; $status2 = "Hard Closed"; $status3 = "Cancelled";
		
		//Export All PRs
		$export1 = mysql_query("SELECT COUNT(*) AS count FROM partrequests AS p, weborders AS w  WHERE p.weborder_id = w.id AND w.run_id = '$id'");
		$countexport1 = mysql_fetch_object($export1)->count;
		
		//Export Open PRs
		$export2 = mysql_query("SELECT COUNT(*) AS count FROM partrequests AS p, weborders AS w  WHERE p.weborder_id = w.id AND p.xto NOT IN ('$status1','$status2','$status3') AND w.run_id = '$id'");
		$countexport2 = mysql_fetch_object($export2)->count;
		
		//Export Closed PRs
		$export3 = mysql_query("SELECT COUNT(*) AS count FROM partrequests AS p, weborders AS w  WHERE p.weborder_id = w.id AND p.xto = '$status1' AND w.run_id = '$id'");
		$countexport3 = mysql_fetch_object($export3)->count;
				
		//Export Cancelled PRs
		$export4 = mysql_query("SELECT COUNT(*) AS count FROM partrequests AS p, weborders AS w  WHERE p.weborder_id = w.id AND p.xto = '$status3' AND w.run_id = '$id'");
		$countexport4 = mysql_fetch_object($export4)->count;
		
		$this->set(compact('result','result2count','result3count','result31count','result4count','result41count','result5count','result51count','result6count','result61count','result10','countexport1','countexport2','countexport3','countexport4'));
		
				
		
	}

	function add() {
		if (!empty($this->data)) {
			$this->Run->create();
			if ($this->Run->save($this->data)) {
				$this->Session->setFlash(__('The run has been saved', true));
				$this->redirect(array('action' => 'index'));
			} else {
				$this->Session->setFlash(__('The run could not be saved. Please, try again.', true));
			}
		}
	}

	function edit($id = null) {
		if (!$id && empty($this->data)) {
			$this->Session->setFlash(__('Invalid run', true));
			$this->redirect(array('action' => 'index'));
		}
		if (!empty($this->data)) {
			if ($this->Run->save($this->data)) {
				$this->Session->setFlash(__('The run has been saved', true));
				$this->redirect(array('action' => 'index'));
			} else {
				$this->Session->setFlash(__('The run could not be saved. Please, try again.', true));
			}
		}
		if (empty($this->data)) {
			$this->data = $this->Run->read(null, $id);
		}
	}
	
	function edit2($id = null) {
		if (empty($this->data)) {
			$id = $this->params['url']['runid'];
			$runid = $id;
			$this->set(compact('runid')); 
		}
		if(!empty($this->params['url']['redo'])) { $redo = $this->params['url']['redo']; } else { $redo = 0; }
		
			if (!$id && empty($this->data)) {
				$this->redirect(array('controller' => 'massswap', 'action' => 'step17', '?' => array('runid' => $runid)));
			}
			if (!empty($this->data)) {
				$runid = $this->data['Run']['runid'];
				if ($this->Run->save($this->data)) {
					$this->Session->setFlash(__('The run has been saved', true));
					mysql_query("INSERT INTO `mmst_helper`.`runshasstatusruns` (`id`, `run_id`, `status_id`, `date`) VALUES (NULL, '$runid', '18', NOW())");
					$this->redirect(array('controller' => 'massswap', 'action' => 'step19', '?' => array('runid' => $runid)));
				}
				else {
					$this->Session->setFlash(__('The run could not be saved. Please, try again.', true));
				}
			}
			if (empty($this->data)) {
				$this->data = $this->Run->read(null, $id);
			}
	    
	}
	

	function delete($id = null) {
		if (!$id) {
			$this->Session->setFlash(__('Invalid id for run', true));
			$this->redirect(array('action'=>'index'));
		}
		if ($this->Run->delete($id)) {
			$this->Session->setFlash(__('Run deleted', true));
			$this->redirect(array('action'=>'index'));
		}
		$this->Session->setFlash(__('Run was not deleted', true));
		$this->redirect(array('action' => 'index'));
	}
	
	
	function deleteall($id = null) {
		if (!$id) {
			$this->Session->setFlash(__('Invalid id for run', true));
			$this->redirect(array('action'=>'overview'));
		}
		else {
			
			//delete uploaded files
			$result1 = mysql_query("SELECT file_id, id FROM runshasfiles WHERE run_id = '$id'");
			WHILE($row1 = mysql_fetch_array($result1)){
				$fileid = $row1["file_id"];
				$rhfid = $row1["id"];
				mysql_query("DELETE FROM files WHERE id = '$fileid' LIMIT 1");
				mysql_query("DELETE FROM runshasfiles WHERE file_id = '$rhfid' LIMIT 1");
			}
			
			//delete pending orders
			$result2 = mysql_query("SELECT c.id FROM runshasmacs AS rhm, macs AS m, customershasmacs AS chm, customers AS c WHERE rhm.run_id = '$id' AND rhm.mac_id = m.id AND m.id = chm.mac_id AND chm.customer_id = c.id");
			WHILE($row2 = mysql_fetch_array($result2)) {
				$cid = $row2["id"];
				$result3 = mysql_query("SELECT p.id AS pid, phc.id AS phcid FROM pendingordershascustomers AS phc, pendingorders AS p WHERE phc.pendingorder_id = p.id AND phc.customer_id = '$cid'");
				WHILE($row3 = mysql_fetch_array($result3)) {
					$pid = $row3["pid"];
					$phcid = $row3["phcid"]; 
					mysql_query("DELETE FROM pendingorders WHERE id = '$pid' LIMIT 1");
					mysql_query("DELETE FROM pendingordershascustomers WHERE id = '$phcid' LIMIT 1");
				}
			}
			
			//delete products of customers
			$result4 = mysql_query("SELECT c.id FROM runshasmacs AS rhm, macs AS m, customershasmacs AS chm, customers AS c WHERE rhm.run_id = '$id' AND rhm.mac_id = m.id AND m.id = chm.mac_id AND chm.customer_id = c.id");
			WHILE($row4 = mysql_fetch_array($result4)) {
				$cid = $row4["id"];
				$result5 = mysql_query("SELECT id FROM productshascustomers WHERE customer_id = '$cid'");
				WHILE($row5 = mysql_fetch_array($result5)) {
					$pid = $row5["id"];
					mysql_query("DELETE FROM productshascustomers WHERE id = '$pid' LIMIT 1");
				}
			}
			
			//delete customers
			$result6 = mysql_query("SELECT c.id FROM runshasmacs AS rhm, macs AS m, customershasmacs AS chm, customers AS c WHERE rhm.run_id = '$id' AND rhm.mac_id = m.id AND m.id = chm.mac_id AND chm.customer_id = c.id");
			WHILE($row6 = mysql_fetch_array($result6)) {
				$cid = $row6["id"];
				mysql_query("DELETE FROM customers WHERE id = '$cid' LIMIT 1");
			}
			
			//delete macs & blacklist
			$result7 = mysql_query("SELECT m.id FROM runshasmacs AS rhm, macs AS m WHERE rhm.run_id = '$id' AND rhm.mac_id = m.id");
			WHILE($row7 = mysql_fetch_array($result7)) {
				$mid = $row7["id"];
				mysql_query("DELETE FROM customershasmacs WHERE mac_id = '$mid'");
				mysql_query("DELETE FROM macs WHERE id = '$mid'");
				mysql_query("DELETE FROM blacklists WHERE mac_id = '$mid'");
			}
				
			//delete runstatus
			mysql_query("DELETE FROM runshasstatusruns WHERE run_id = '$id'");
						
			//delete run
			mysql_query("DELETE FROM runs WHERE id = '$id' LIMIT 1");
			
			
			$this->Session->setFlash(__('Run completly deleted', true));
			$this->redirect(array('action'=>'overview'));
		}
		$this->Session->setFlash(__('Run was not deleted', true));
		$this->redirect(array('action' => 'overview'));
	}
	
	
	function overview(){
		$result = $this->Run->query('SELECT p.name, r.id, s.id, s.order, r.fileid, r.batchid, s.statusname, rhs.date FROM runs AS r, projects AS p, runshasstatusruns AS rhs, statuses AS s, (SELECT max(rhs.id) AS maxid, rhs.run_id AS runid FROM runshasstatusruns AS rhs GROUP BY rhs.run_id) AS x WHERE r.id = rhs.run_id AND s.id = rhs.status_id AND x.runid = r.id AND rhs.id = x.maxid AND statusgroup_id = 1 AND r.project_id = p.id');
		$this->set(compact('result'));	
	}
}
?>